package com.li.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.li.client.entities.BookInfo;
import com.li.client.entities.BookType;
import com.li.client.entities.Publisher;
import com.li.server.jdbc.JDBCExecutor;
import com.li.server.jdbc.JDBCUtil;

public class BookInfoDaoImpl implements BookInfoDao {

	@Override
	public Boolean borrowBook(final String userId, final String isbnCode) {
		return new JDBCUtil().executeSQL(new JDBCExecutor<Boolean>() {

			@Override
			public Boolean execute(Connection conn) throws Exception {
				PreparedStatement queryStmt = conn.prepareStatement("SELECT PK_BOOK_ID FROM BOOK_INFO WHERE ISBN_CODE = ? ");
				queryStmt.setString(1, isbnCode);
				
				int pkBookId = 0;
				ResultSet rs = queryStmt.executeQuery();
				while(rs.next()) {
					pkBookId = rs.getInt(1);
				}
				
				PreparedStatement queryUserStmt = conn.prepareStatement("SELECT PK_USER_ID FROM USER WHERE LOGIN_ID = ? ");
				queryUserStmt.setString(1, userId);
				
				int pkUserId = 0;
				ResultSet rs2 = queryUserStmt.executeQuery();
				while(rs2.next()) {
					pkUserId = rs2.getInt(1);
				}
				
				if (pkBookId == 0 || pkUserId == 0) {
					throw new RuntimeException("user or book is exists");
				}
				PreparedStatement borrowStmt = conn.prepareStatement("INSERT INTO BOOK_BORROW_INFO(PK_USER_ID, PK_BOOK_ID) VALUES (? , ?)");
				PreparedStatement upStmt = conn.prepareStatement("UPDATE BOOK_IN_STOCK SET IN_STOCK = IN_STOCK - 1 WHERE FK_BOOK_ID = ?");
				
				borrowStmt.setInt(1, pkUserId);
				borrowStmt.setInt(2, pkBookId);
				
				upStmt.setInt(1, pkBookId);
				
				borrowStmt.executeUpdate();
				upStmt.executeUpdate();
				
				return Boolean.TRUE;
			}
		});
	}

	@Override
	public List<BookInfo> getAllBookInfo() {
		return new JDBCUtil().executeSQL(new JDBCExecutor<List<BookInfo>>() {

			@Override
			public List<BookInfo> execute(Connection conn)  throws Exception {
				PreparedStatement stmt = conn.prepareStatement("select PK_BOOK_ID, BOOK_NAME, ISBN_CODE, BOOK_PRICE, ENROLL_DATE, IN_STOCK " +
						"FROM BOOK_INFO BI" +
						", BOOK_IN_STOCK BIS " +
						"WHERE BI.PK_BOOK_ID = BIS.FK_BOOK_ID ");
				
				List<BookInfo> rst = new ArrayList<BookInfo>();
				
				ResultSet rs = stmt.executeQuery();
				while(rs.next()) {
					BookInfo bi = new BookInfo();
					bi.setPkBookInfoId(rs.getInt("PK_BOOK_ID"));
					bi.setBookName(rs.getString("BOOK_NAME"));
					bi.setIsbnCode(rs.getString("ISBN_CODE"));
					bi.setBookPrice(rs.getBigDecimal("BOOK_PRICE"));
					bi.setEnrollDate(rs.getDate("ENROLL_DATE"));
					bi.setInStock(rs.getInt("IN_STOCK"));
					
					rst.add(bi);
				}
				
				return rst;
			}
		});
	}

	@Override
	public BookInfo getBookByISBN(final String isbnCode) {
		return new JDBCUtil().executeSQL(new JDBCExecutor<BookInfo>() {

			@Override
			public BookInfo execute(Connection conn) throws Exception {
				PreparedStatement queryStmt = conn.prepareStatement("SELECT PK_BOOK_ID, BOOK_NAME, ISBN_CODE, BOOK_PRICE, ENROLL_DATE, PK_BOOK_TYPE_ID, TYPE_NAME, PK_PUBLISHER_ID, NAME, ADDRESS, ZIP_CODE " +
						"FROM BOOK_INFO, BOOK_TYPE, PUBLISHER " +
						"WHERE ISBN_CODE = ? " +
						" AND FK_PUBLISHER_ID = PK_PUBLISHER_ID" +
						" AND FK_TYPE_ID = PK_BOOK_TYPE_ID");
				queryStmt.setString(1, isbnCode);
				
				ResultSet rs = queryStmt.executeQuery();
				
				BookInfo bi = null;
				BookType bt = null;
				Publisher p = null;
				while(rs.next()) {
					bi = new BookInfo();
					bi.setPkBookInfoId(rs.getInt("PK_BOOK_ID"));
					bi.setBookName(rs.getString("BOOK_NAME"));
					bi.setIsbnCode(rs.getString("ISBN_CODE"));
					bi.setBookPrice(rs.getBigDecimal("BOOK_PRICE"));
					bi.setEnrollDate(rs.getDate("ENROLL_DATE"));
					
					bt = new BookType();
					bt.setPkBookTypeiD(rs.getInt("PK_BOOK_TYPE_ID"));
					bt.setTypeName(rs.getString("TYPE_NAME"));
					
					p = new Publisher();
					p.setPkPublisherId(rs.getInt("PK_PUBLISHER_ID"));
					p.setName(rs.getString("NAME"));
					p.setAddress(rs.getString("ADDRESS"));
					p.setZipCode(rs.getString("ZIP_CODE"));
					
					bi.setBookType(bt);
					bi.setPublisher(p);
				}
				
				return bi;
			}
		});
	}

	@Override
	public Boolean returnBook(final Integer pkUserId, final Integer pkBookId) {
		return new JDBCUtil().executeSQL(new JDBCExecutor<Boolean>() {

			@Override
			public Boolean execute(Connection conn) throws Exception {
				PreparedStatement borrowStmt = conn.prepareStatement("DELETE FROM BOOK_BORROW_INFO WHERE PK_USER_ID = ? AND PK_BOOK_ID = ? ");
				PreparedStatement upStmt = conn.prepareStatement("UPDATE BOOK_IN_STOCK SET IN_STOCK = IN_STOCK + 1 WHERE FK_BOOK_ID = ?");
				
				borrowStmt.setInt(1, pkUserId);
				borrowStmt.setInt(2, pkBookId);
				
				upStmt.setInt(1, pkBookId);
				
				borrowStmt.executeUpdate();
				upStmt.executeUpdate();
				
				return Boolean.TRUE;
			}
			
		});
	}

	@Override
	public Integer getBookInStock(final String isbnCode) {
		return new JDBCUtil().executeSQL(new JDBCExecutor<Integer>(){

			@Override
			public Integer execute(Connection conn) throws Exception {
				PreparedStatement stmt = conn.prepareStatement("SELECT IN_STOCK FROM BOOK_IN_STOCK BIS, BOOK_INFO BI WHERE BI.ISBN_CODE = ? AND BI.PK_BOOK_ID = BIS.FK_BOOK_ID ");
				stmt.setString(1, isbnCode);
				
				ResultSet rs = stmt.executeQuery();
				int result = -1;
				while(rs.next()) {
					result = rs.getInt("IN_STOCK");
				}
				rs.close();
				return result;
			}});
	}

	@Override
	public Boolean batchInsert(final List<BookInfo> bookInfoList) {
		return new JDBCUtil().executeSQL(new JDBCExecutor<Boolean>(){

			@Override
			public Boolean execute(Connection conn) throws Exception {
				PreparedStatement ps = conn.prepareStatement("INSERT INTO BOOK_INFO( BOOK_NAME, ISBN_CODE, BOOK_PRICE, ENROLL_DATE, FK_PUBLISHER_ID, FK_TYPE_ID) " +
						"VALUES(?, ?, ?, ?, ?, ?)");
				
				for (BookInfo bookInfo : bookInfoList) {
					ps.setString(1, bookInfo.getBookName());
					ps.setString(2, bookInfo.getIsbnCode());
					ps.setBigDecimal(3, bookInfo.getBookPrice());
					ps.setDate(4, new java.sql.Date(new Date().getTime()));
					ps.setInt(5, bookInfo.getPublisher().getPkPublisherId());
					ps.setInt(6, bookInfo.getBookType().getPkBookTypeiD());
					
					ps.addBatch();
					ps.clearParameters();
				}
				
				ps.executeBatch();
				ps.close();
				return Boolean.TRUE;
			}
		});


	}
}
